<?php
namespace app\index\controller;

use think\Controller;
use think\Db;
use think\facade\Log;
use PhpOffice\PhpSpreadsheet\IOFactory;

/**
 * Excel数据处理控制器
 * 将Excel数据导入到多语言中医症状汤药数据库
 *
 * 处理流程：
 * 1. D列分类数据处理
 * 2. B列+C列症状数据处理（带语义化匹配）
 * 3. A列汤药数据处理
 */
class Excelprocessor extends Controller
{
    /**
     * 多语言翻译映射表
     */
    private $translations = [
        'en' => [
            '头颈部症状' => 'Head & Neck Symptoms',
            '呼吸系统症状' => 'Respiratory Symptoms',
            '消化系统症状' => 'Digestive Symptoms',
            '循环系统症状' => 'Circulatory Symptoms',
            '其他症状' => 'Other Symptoms',
            '神经系统症状' => 'Nervous System Symptoms',
            '泌尿系统症状' => 'Urinary System Symptoms',
            '妇科症状' => 'Gynecological Symptoms',
            '皮肤症状' => 'Skin Symptoms',

            // 症状翻译
            '头痛' => 'Headache',
            '头晕' => 'Dizziness',
            '咳嗽' => 'Cough',
            '恶心' => 'Nausea',
            '发热' => 'Fever',
            '失眠' => 'Insomnia',
            '腹痛' => 'Abdominal Pain',
            '便秘' => 'Constipation',
            '腹泻' => 'Diarrhea',
            '心悸' => 'Palpitation',
            '胸闷' => 'Chest Tightness',
            '气短' => 'Shortness of Breath',
            '疲劳' => 'Fatigue',
            '食欲不振' => 'Loss of Appetite',
            '恶寒' => 'Aversion to Cold',
            '汗出' => 'Sweating',
            '气喘' => 'Asthma',
            '无汗' => 'Anhidrosis',
            '咽痛' => 'Sore Throat',
            '口渴' => 'Thirst',
            '身痛' => 'Body Aches',
            '烦躁' => 'Irritability',
            '小便不利' => 'Dysuria',
            '水肿' => 'Edema',
            '腹胀' => 'Abdominal Distension',
            '口淡' => 'Bland Taste',
            '便溏' => 'Loose Stool',
            '胸胁胀痛' => 'Chest and Hypochondriac Distention and Pain',
            '抑郁' => 'Depression',
            '月经不调' => 'Irregular Menstruation',

            // 汤药翻译
            '桂枝汤' => 'Cinnamon Twig Decoction',
            '小青龙汤' => 'Minor Bluegreen Dragon Decoction',
            '银翘散' => 'Honeysuckle and Forsythia Powder',
            '麻黄汤' => 'Ephedra Decoction',
            '大青龙汤' => 'Major Bluegreen Dragon Decoction',
            '五苓散' => 'Five-Ingredient Powder with Poria',
            '平胃散' => 'Stomach-Calming Powder',
            '六君子汤' => 'Six-Gentleman Decoction',
            '逍遥散' => 'Free and Easy Wanderer Powder',
            '补中益气汤' => 'Tonify the Middle and Augment the Qi Decoction'
        ],
        'ja' => [
            '头颈部症状' => '頭頸部症状',
            '呼吸系统症状' => '呼吸器症状',
            '消化系统症状' => '消化器症状',
            '循环系统症状' => '循環器症状',
            '其他症状' => 'その他の症状',
            '神经系统症状' => '神経系症状',
            '泌尿系统症状' => '泌尿器症状',
            '妇科症状' => '婦人科症状',
            '皮肤症状' => '皮膚症状',

            // 症状翻译
            '头痛' => '頭痛',
            '头晕' => 'めまい',
            '咳嗽' => '咳',
            '恶心' => '吐き気',
            '发热' => '発熱',
            '失眠' => '不眠症',
            '腹痛' => '腹痛',
            '便秘' => '便秘',
            '腹泻' => '下痢',
            '心悸' => '動悸',
            '胸闷' => '胸苦しさ',
            '气短' => '息切れ',
            '疲劳' => '疲労',
            '食欲不振' => '食欲不振',
            '恶寒' => '悪寒',
            '汗出' => '発汗',
            '气喘' => '喘息',
            '无汗' => '無汗',
            '咽痛' => '咽頭痛',
            '口渴' => '口渇',
            '身痛' => '身体痛',
            '烦躁' => '煩燥',
            '小便不利' => '排尿困難',
            '水肿' => '浮腫',
            '腹胀' => '腹部膨満',
            '口淡' => '口淡',
            '便溏' => '軟便',
            '胸胁胀痛' => '胸脇脹痛',
            '抑郁' => '抑うつ',
            '月经不调' => '月経不順',

            // 汤药翻译
            '桂枝汤' => '桂枝湯',
            '小青龙汤' => '小青竜湯',
            '银翘散' => '銀翹散',
            '麻黄汤' => '麻黄湯',
            '大青龙汤' => '大青竜湯',
            '五苓散' => '五苓散',
            '平胃散' => '平胃散',
            '六君子汤' => '六君子湯',
            '逍遥散' => '逍遥散',
            '补中益气汤' => '補中益気湯'
        ],
        'be' => [
            '头颈部症状' => 'Сімптомы галавы і шыі',
            '呼吸系统症状' => 'Сімптомы дыхальнай сістэмы',
            '消化系统症状' => 'Сімптомы стрававальнай сістэмы',
            '循环系统症状' => 'Сімптомы кровазвярнення',
            '其他症状' => 'Іншыя сімптомы',
            '神经系统症状' => 'Сімптомы нервовай сістэмы',
            '泌尿系统症状' => 'Сімптомы мачаполавой сістэмы',
            '妇科症状' => 'Гінекалагічныя сімптомы',
            '皮肤症状' => 'Сімптомы скуры',

            // 症状翻译
            '头痛' => 'Галаўны боль',
            '头晕' => 'Запаморкі',
            '咳嗽' => 'Кашаль',
            '恶心' => 'Млосць',
            '发热' => 'Ліхаманка',
            '失眠' => 'Бяссонніца',
            '腹痛' => 'Боль у жываце',
            '便秘' => 'Закрэп',
            '腹泻' => 'Панос',
            '心悸' => 'Сэрцабіенне',
            '胸闷' => 'Цяжкасць у грудзях',
            '气短' => 'Задышка',
            '疲劳' => 'Стомленасць',
            '食欲不振' => 'Страта апетыту',
            '恶寒' => 'Непераносімасць холаду',
            '汗出' => 'Пацення',
            '气喘' => 'Удушша',
            '无汗' => 'Адсутнасць поту',
            '咽痛' => 'Боль у горле',
            '口渴' => 'Смага',
            '身痛' => 'Боль у целе',
            '烦躁' => 'Раздражняльнасць',
            '小便不利' => 'Парушэнне мачавыпускання',
            '水肿' => 'Набрыяк',
            '腹胀' => 'Здзіманне жывата',
            '口淡' => 'Безгустоўнасць',
            '便溏' => 'Мяккі стул',
            '胸胁胀痛' => 'Боль і расцяжэнне ў грудзях і бакох',
            '抑郁' => 'Дэпрэсія',
            '月经不调' => 'Парушэнне менструацыі',

            // 汤药翻译
            '桂枝汤' => 'Адвар корніца карыцы',
            '小青龙汤' => 'Малы сіні дракон адвар',
            '银翘散' => 'Парашок жымолость і форзіцыя',
            '麻黄汤' => 'Адвар эфедры',
            '大青龙汤' => 'Вялікі сіні дракон адвар',
            '五苓散' => 'Парашок пяці кампанентаў з порыяй',
            '平胃散' => 'Парашок для супакаення страўніка',
            '六君子汤' => 'Адвар шасці шляхетных',
            '逍遥散' => 'Парашок вольнага вандроўніка',
            '补中益气汤' => 'Адвар для ўмацавання сярэдняга і павелічэння ці'
        ]
    ];

    /**
     * 症状分类关键词映射表（用于语义化匹配）
     */
    private $symptomCategoryKeywords = [
        '头颈部症状' => ['头痛', '头晕', '头重', '眩晕', '目眩', '颈项', '咽', '喉', '口', '舌', '牙', '齿'],
        '呼吸系统症状' => ['咳嗽', '气喘', '咳痰', '胸闷', '胸痛', '气短', '呼吸', '肺', '鼻', '痰', '哮', '喘'],
        '消化系统症状' => ['腹痛', '腹胀', '恶心', '呕吐', '食欲', '便秘', '腹泻', '便溏', '胃', '肠', '脾', '口淡', '纳'],
        '循环系统症状' => ['心悸', '胸痛', '心慌', '心律', '血压', '水肿', '小便不利', '心', '血', '脉', '汗'],
        '神经系统症状' => ['失眠', '烦躁', '抑郁', '健忘', '神疲', '精神', '情志', '焦虑', '惊悸'],
        '妇科症状' => ['月经', '带下', '经期', '闭经', '痛经', '崩漏', '产后', '妊娠'],
        '泌尿系统症状' => ['小便', '尿', '遗尿', '尿频', '尿急', '尿痛', '肾', '膀胱'],
        '皮肤症状' => ['皮疹', '瘙痒', '湿疹', '疮疡', '斑疹', '皮肤', '毛发']
    ];

    /**
     * 处理首页 - 显示处理状态和操作按钮
     */
    public function index()
    {
        return $this->fetch('excel_processor/index');
    }

    /**
     * 主处理入口
     * 处理Excel数据并导入到数据库
     */
    public function processExcelData()
    {
        // 设置响应头确保UTF-8编码
        header('Content-Type: application/json; charset=utf-8');

        try {
            // 从Excel文件读取数据
            $excelData = $this->readExcelData();

            if (empty($excelData)) {
                return json([
                    'code' => 400,
                    'msg' => 'Excel数据为空',
                    'data' => null
                ]);
            }

            $result = [
                'categories_processed' => 0,
                'symptoms_processed' => 0,
                'formulas_processed' => 0,
                'errors' => [],
                'data_count' => count($excelData)
            ];

            Log::info("开始处理Excel数据，共 " . count($excelData) . " 行");

            // 使用事务确保数据一致性
            Db::startTrans();
            try {
                // 阶段1：处理D列分类数据（false = 真正插入数据库）
                $categoriesResult = $this->processCategories($excelData, false); // 改为false，真正插入
                $result['categories_processed'] = isset($categoriesResult['processed']) ? $categoriesResult['processed'] : 0;
                $result['categories_skipped'] = isset($categoriesResult['skipped']) ? $categoriesResult['skipped'] : 0;
                $result['categories_data'] = isset($categoriesResult['data']) ? $categoriesResult['data'] : [];
                if (isset($categoriesResult['errors']) && is_array($categoriesResult['errors'])) {
                    $result['errors'] = array_merge($result['errors'], $categoriesResult['errors']);
                } elseif (isset($categoriesResult['errors'])) {
                    $result['errors'][] = $categoriesResult['errors'];
                }

                // 获取分类ID映射
                $categoryIdMap = isset($categoriesResult['category_map']) ? $categoriesResult['category_map'] : [];

                // 阶段2：处理B列+C列症状数据，传入分类ID映射（false = 真正插入数据库）
                $symptomsResult = $this->processSymptoms($excelData, false, $categoryIdMap); // 改为false，真正插入
                $result['symptoms_processed'] = isset($symptomsResult['processed']) ? $symptomsResult['processed'] : 0;
                $result['symptoms_skipped'] = isset($symptomsResult['skipped']) ? $symptomsResult['skipped'] : 0;
                $result['symptoms_data'] = isset($symptomsResult['data']) ? $symptomsResult['data'] : [];
                if (isset($symptomsResult['errors']) && is_array($symptomsResult['errors'])) {
                    $result['errors'] = array_merge($result['errors'], $symptomsResult['errors']);
                } elseif (isset($symptomsResult['errors'])) {
                    $result['errors'][] = $symptomsResult['errors'];
                }

                // 阶段3：处理A列汤药数据（false = 真正插入数据库）
                $formulasResult = $this->processFormulas($excelData, false); // 改为false，真正插入
                $result['formulas_processed'] = isset($formulasResult['processed']) ? $formulasResult['processed'] : 0;
                $result['formulas_skipped'] = isset($formulasResult['skipped']) ? $formulasResult['skipped'] : 0;
                $result['formulas_data'] = isset($formulasResult['data']) ? $formulasResult['data'] : [];
                if (isset($formulasResult['errors']) && is_array($formulasResult['errors'])) {
                    $result['errors'] = array_merge($result['errors'], $formulasResult['errors']);
                } elseif (isset($formulasResult['errors'])) {
                    $result['errors'][] = $formulasResult['errors'];
                }

                // 提交事务
                Db::commit();

                Log::info("Excel数据处理完成（真实模式）", $result);

                // 确保返回的数据是UTF-8安全的
                $safeResult = $this->ensureUtf8Safe($result);

                // 添加汇总信息
                $result['summary'] = [
                    'categories' => [
                        'new' => $result['categories_processed'],
                        'exists' => $result['categories_skipped'],
                        'total' => $result['categories_processed'] + $result['categories_skipped']
                    ],
                    'symptoms' => [
                        'new' => $result['symptoms_processed'],
                        'exists' => $result['symptoms_skipped'],
                        'total' => $result['symptoms_processed'] + $result['symptoms_skipped']
                    ],
                    'formulas' => [
                        'new' => $result['formulas_processed'],
                        'exists' => $result['formulas_skipped'],
                        'total' => $result['formulas_processed'] + $result['formulas_skipped']
                    ]
                ];

                return json([
                    'code' => 200,
                    'msg' => '数据处理完成，所有数据已插入数据库！',
                    'data' => $safeResult
                ]);

            } catch (\Exception $e) {
                // 回滚事务
                Db::rollback();
                Log::error("数据处理错误，事务已回滚: " . $e->getMessage());
                throw $e;
            }

        } catch (\Exception $e) {
            Log::error('Excel数据处理失败：' . $e->getMessage());

            return json([
                'code' => 500,
                'msg' => 'Excel数据处理失败：' . mb_convert_encoding($e->getMessage(), 'UTF-8', 'UTF-8'),
                'data' => null
            ]);
        }
    }

    /**
     * 确保数据是UTF-8安全的
     */
    private function ensureUtf8Safe($data)
    {
        if (is_array($data)) {
            $safe = [];
            foreach ($data as $key => $value) {
                $safeKey = $this->cleanCellValue($key);
                $safe[$safeKey] = $this->ensureUtf8Safe($value);
            }
            return $safe;
        } elseif (is_string($data)) {
            return $this->cleanCellValue($data);
        } else {
            return $data;
        }
    }

    /**
     * 阶段1：处理D列分类数据
     */
    private function processCategories($excelData, $debug = false)
    {
        $result = ['processed' => 0, 'errors' => [], 'data' => [], 'skipped' => 0, 'category_map' => []];

        // 收集所有分类
        $allCategories = [];
        foreach ($excelData as $row) {
            $categories = $this->splitByComma($row[3] ?? ''); // D列
            if (is_array($categories) && !empty($categories)) {
                $allCategories = array_merge($allCategories, $categories);
            }
        }

        $allCategories = array_unique(array_filter($allCategories));

        // 构建分类名称到ID的映射
        $categoryIdMap = [];

        foreach ($allCategories as $category) {
            try {
                // 检查分类是否已存在
                $existingCategory = Db::name('symptom_categories')
                    ->where('name_zh_cn', $category)
                    ->find();

                if ($existingCategory) {
                    // 分类已存在，记录ID映射
                    $categoryIdMap[$category] = $existingCategory['id'];
                    Log::info("分类已存在，ID={$existingCategory['id']}：{$category}");
                    $result['skipped']++;

                    // 即使存在，在debug模式下也要收集数据以显示
                    if ($debug) {
                        $result['data'][] = [
                            'id' => $existingCategory['id'],
                            'name_zh_cn' => $existingCategory['name_zh_cn'],
                            'name_en_us' => $existingCategory['name_en_us'],
                            'name_ja_jp' => $existingCategory['name_ja_jp'],
                            'name_be_by' => $existingCategory['name_be_by'],
                            'status' => 'exists',
                            'action' => 'skip'
                        ];
                    }
                } else {
                    // 生成多语言翻译
                    $translations = $this->generateTranslations($category);

                    // 准备插入数据
                    $categoryData = [
                        'name_zh_cn' => $category,
                        'name_en_us' => $translations['en'],
                        'name_ja_jp' => $translations['ja'],
                        'name_be_by' => $translations['be'],
                        'description_zh_cn' => $category . '相关症状',
                        'description_en_us' => $translations['en'] . ' related symptoms',
                        'description_ja_jp' => $translations['ja'] . '関連症状',
                        'description_be_by' => $translations['be'] . ' звязаныя сімптомы',
                        'sort_order' => 100,
                        'is_active' => 1,
                        'created_at' => date('Y-m-d H:i:s'),
                        'updated_at' => date('Y-m-d H:i:s')
                    ];

                    if ($debug) {
                        // 调试模式：只收集数据，不插入数据库
                        $categoryData['status'] = 'new';
                        $categoryData['action'] = 'will_insert';
                        $result['data'][] = $categoryData;
                        Log::info("收集分类数据（待插入）：{$category}");
                    } else {
                        // 正常模式：插入数据库
                        $categoryId = Db::name('symptom_categories')->insertGetId($categoryData);
                        $categoryIdMap[$category] = $categoryId;
                        Log::info("创建新分类，ID={$categoryId}：{$category}");
                    }

                    $result['processed']++;
                }

            } catch (\Exception $e) {
                $result['errors'][] = "处理分类 '{$category}' 失败：" . $e->getMessage();
                Log::error("处理分类失败：{$category} - " . $e->getMessage());
            }
        }

        // 返回分类ID映射，供后续症状处理使用
        $result['category_map'] = $categoryIdMap;
        return $result;
    }

    /**
     * 阶段2：处理B列+C列症状数据
     */
    private function processSymptoms($excelData, $debug = false, $categoryIdMap = [])
    {
        $result = ['processed' => 0, 'errors' => [], 'data' => [], 'skipped' => 0];

        // 如果没有传入分类ID映射，从数据库构建
        if (empty($categoryIdMap)) {
            $categories = Db::name('symptom_categories')->select();
            foreach ($categories as $cat) {
                $categoryIdMap[$cat['name_zh_cn']] = $cat['id'];
            }
            Log::info("从数据库加载分类映射，共" . count($categoryIdMap) . "个分类");
        }

        // 收集所有症状及其对应的分类信息（使用数组避免重复）
        $symptomCategoryMap = [];
        $processedSymptoms = []; // 记录已处理的症状，避免重复

        foreach ($excelData as $rowIndex => $row) {
            $rowCategories = $this->splitByComma($row[3] ?? ''); // D列分类
            if (!is_array($rowCategories)) $rowCategories = [];

            // 处理主证状（B列）
            $mainSymptoms = $this->splitByComma($row[1] ?? '');
            if (is_array($mainSymptoms)) {
                foreach ($mainSymptoms as $symptom) {
                    // 确保症状不为空且未被处理过
                    if (!empty($symptom) && !isset($symptomCategoryMap[$symptom])) {
                        $symptomCategoryMap[$symptom] = $rowCategories;
                        Log::info("从B列读取症状（行{$rowIndex}）: '{$symptom}'");
                    }
                }
            }

            // 处理兼症状（C列）
            $sideSymptoms = $this->splitByComma($row[2] ?? '');
            if (is_array($sideSymptoms)) {
                foreach ($sideSymptoms as $symptom) {
                    // 确保症状不为空且未被处理过
                    if (!empty($symptom) && !isset($symptomCategoryMap[$symptom])) {
                        $symptomCategoryMap[$symptom] = $rowCategories;
                        Log::info("从C列读取症状（行{$rowIndex}）: '{$symptom}'");
                    }
                }
            }
        }

        Log::info("症状收集完成，共收集到 " . count($symptomCategoryMap) . " 个不重复的症状");

        // 记录已处理的症状，避免在数据库中重复
        $addedSymptoms = [];

        foreach ($symptomCategoryMap as $symptom => $rowCategories) {
            try {
                // 二次检查，确保不处理重复项
                if (in_array($symptom, $addedSymptoms)) {
                    Log::warning("症状已在本批次处理，跳过: '{$symptom}'");
                    continue;
                }

                // 检查症状是否已存在
                $existingSymptom = Db::name('symptoms')
                    ->where('name_zh_cn', $symptom)
                    ->find();

                if ($existingSymptom) {
                    // 症状已存在
                    Log::info("症状已存在，ID={$existingSymptom['id']}：{$symptom}");
                    $result['skipped']++;

                    if ($debug) {
                        $result['data'][] = [
                            'id' => $existingSymptom['id'],
                            'name_zh_cn' => $existingSymptom['name_zh_cn'],
                            'category_id' => $existingSymptom['category_id'],
                            'status' => 'exists',
                            'action' => 'skip'
                        ];
                    }
                    continue;
                }

                // 症状不存在，准备新增
                if (!$existingSymptom) {
                    // 从分类ID映屍中查找对应的分类ID
                    $categoryId = null;
                    if (!empty($rowCategories)) {
                        foreach ($rowCategories as $categoryName) {
                            if (isset($categoryIdMap[$categoryName])) {
                                $categoryId = $categoryIdMap[$categoryName];
                                Log::info("症状'{$symptom}'匹配到分类'{$categoryName}'(ID={$categoryId})");
                                break;
                            }
                        }
                    }

                    // 如果没有直接匹配，进行语义化分类匹配
                    if (!$categoryId) {
                        $categoryId = $this->findCategoryForSymptom($symptom, $rowCategories, $categoryIdMap);
                    }

                    // 生成症状数据（即使没有分类ID也要处理）
                    if (!$categoryId) {
                        Log::warning("症状'{$symptom}'没有找到合适的分类，category_id将为NULL");
                        $categoryId = null; // 允许为空
                    }

                    // 生成多语言翻译
                    $translations = $this->generateTranslations($symptom);

                    // 准备症状数据
                    $symptomData = [
                        'category_id' => $categoryId,
                        'name_zh_cn' => $symptom,
                        'name_en_us' => $translations['en'],
                        'name_ja_jp' => $translations['ja'],
                        'name_be_by' => $translations['be'],
                        'description_zh_cn' => $symptom . '的症状描述',
                        'description_en_us' => $translations['en'] . ' symptom description',
                        'description_ja_jp' => $translations['ja'] . 'の症状説明',
                        'description_be_by' => $translations['be'] . ' апісанне сімптому',
                        'keywords_zh_cn' => $symptom,
                        'keywords_en_us' => $translations['en'],
                        'keywords_ja_jp' => $translations['ja'],
                        'keywords_be_by' => $translations['be'],
                        'sort_order' => 100,
                        'is_active' => 1,
                        'created_at' => date('Y-m-d H:i:s'),
                        'updated_at' => date('Y-m-d H:i:s')
                    ];

                    if ($debug) {
                        // 调试模式：只收集数据，不插入数据库
                        $symptomData['status'] = 'new';
                        $symptomData['action'] = 'will_insert';
                        $result['data'][] = $symptomData;
                        Log::info("收集症状数据（待插入）：{$symptom}，分类ID：{$categoryId}");
                    } else {
                        // 正常模式：插入数据库
                        Db::name('symptoms')->insert($symptomData);
                        Log::info("创建新症状：{$symptom}，分类ID：{$categoryId}");
                    }

                    $result['processed']++;
                    $addedSymptoms[] = $symptom; // 记录已处理的症状
                }

            } catch (\Exception $e) {
                $result['errors'][] = "处理症状 '{$symptom}' 失败：" . $e->getMessage();
                Log::error("处理症状失败：{$symptom} - " . $e->getMessage());
            }
        }

        return $result;
    }

    /**
     * 阶段3：处理A列汤药数据
     */
    private function processFormulas($excelData, $debug = false)
    {
        $result = ['processed' => 0, 'errors' => [], 'data' => [], 'skipped' => 0];

        foreach ($excelData as $row) {
            $formulaName = trim($row[0] ?? ''); // A列
            if (empty($formulaName)) continue;

            try {
                // 检查汤药是否已存在
                $existingFormula = Db::name('formulas')
                    ->where('name_zh_cn', $formulaName)
                    ->find();

                if ($existingFormula) {
                    // 汤药已存在
                    Log::info("汤药已存在，ID={$existingFormula['id']}：{$formulaName}");
                    $result['skipped']++;

                    if ($debug) {
                        $result['data'][] = [
                            'id' => $existingFormula['id'],
                            'name_zh_cn' => $existingFormula['name_zh_cn'],
                            'status' => 'exists',
                            'action' => 'skip'
                        ];
                    }
                    continue;
                }

                if (!$existingFormula || $debug) {
                    // 获取主证状和兼症状ID
                    $mainSymptoms = $this->splitByComma($row[1] ?? ''); // B列
                    $sideSymptoms = $this->splitByComma($row[2] ?? ''); // C列

                    // 获取症状ID
                    $mainSymptomIds = [];
                    $sideSymptomIds = [];

                    // 无论是否debug模式，都从数据库获取真实的症状ID
                    foreach ($mainSymptoms as $symptom) {
                        if (empty($symptom)) continue;

                        $existingSymptom = Db::name('symptoms')
                            ->where('name_zh_cn', $symptom)
                            ->find();
                        if ($existingSymptom) {
                            $mainSymptomIds[] = $existingSymptom['id'];
                            Log::info("找到主症状ID：{$symptom} => {$existingSymptom['id']}");
                        } else {
                            Log::warning("主症状在数据库中不存在：{$symptom}");
                        }
                    }

                    foreach ($sideSymptoms as $symptom) {
                        if (empty($symptom)) continue;

                        $existingSymptom = Db::name('symptoms')
                            ->where('name_zh_cn', $symptom)
                            ->find();
                        if ($existingSymptom) {
                            $sideSymptomIds[] = $existingSymptom['id'];
                            Log::info("找到兼症状ID：{$symptom} => {$existingSymptom['id']}");
                        } else {
                            Log::warning("兼症状在数据库中不存在：{$symptom}");
                        }
                    }

                    // 生成多语言翻译
                    $translations = $this->generateTranslations($formulaName);

                    // 准备汤药数据
                    $formulaData = [
                        'name_zh_cn' => $formulaName,
                        'name_en_us' => $translations['en'],
                        'name_ja_jp' => $translations['ja'],
                        'name_be_by' => $translations['be'],
                        'description_zh_cn' => $formulaName . '的中医汤药',
                        'description_en_us' => $translations['en'] . ' Traditional Chinese Medicine',
                        'description_ja_jp' => $translations['ja'] . 'の中医学薬',
                        'description_be_by' => $translations['be'] . ' традыцыйная кітайская медыцына',
                        'ingredients_zh_cn' => '具体配方待补充',
                        'ingredients_en_us' => 'Specific formula to be added',
                        'ingredients_ja_jp' => '具体的な処方は後で追加',
                        'ingredients_be_by' => 'Канкрэтная формула будзе дададзена',
                        'usage_method_zh_cn' => '煎服方法待补充',
                        'usage_method_en_us' => 'Decoction method to be added',
                        'usage_method_ja_jp' => '煎じ方は後で追加',
                        'usage_method_be_by' => 'Метад прыгатавання будзе дададзены',
                        'effects_zh_cn' => '功效作用待补充',
                        'effects_en_us' => 'Effects to be added',
                        'effects_ja_jp' => '効果は後で追加',
                        'effects_be_by' => 'Эфект будзе дададзены',
                        'main_symptom_ids' => !empty($mainSymptomIds) ? implode(',', $mainSymptomIds) : '',
                        'side_symptom_ids' => !empty($sideSymptomIds) ? implode(',', $sideSymptomIds) : '',
                        'sort_order' => 100,
                        'is_active' => 1,
                        'created_at' => date('Y-m-d H:i:s'),
                        'updated_at' => date('Y-m-d H:i:s')
                    ];

                    if ($debug) {
                        // 调试模式：只收集数据，不插入数据库
                        $formulaData['status'] = 'new';
                        $formulaData['action'] = 'will_insert';
                        // 添加调试信息
                        $formulaData['debug_info'] = [
                            'main_symptoms_list' => $mainSymptoms,
                            'side_symptoms_list' => $sideSymptoms,
                            'main_symptom_ids_found' => $mainSymptomIds,
                            'side_symptom_ids_found' => $sideSymptomIds
                        ];
                        $result['data'][] = $formulaData;
                        Log::info("收集汤药数据（待插入）：{$formulaName}");
                    } else {
                        // 正常模式：插入数据库
                        Db::name('formulas')->insert($formulaData);
                        Log::info("创建新汤药：{$formulaName}");
                    }

                    $result['processed']++;
                }

            } catch (\Exception $e) {
                $result['errors'][] = "处理汤药 '{$formulaName}' 失败：" . $e->getMessage();
                Log::error("处理汤药失败：{$formulaName} - " . $e->getMessage());
            }
        }

        return $result;
    }

    /**
     * 语义化分类匹配
     * 为症状找到最合适的分类ID
     */
    private function findCategoryForSymptom($symptom, $rowCategories, $categoryIdMap = [])
    {
        // 1. 优先匹配：使用该症状所在行的D列分类
        if (!empty($rowCategories)) {
            foreach ($rowCategories as $category) {
                if (isset($categoryIdMap[$category])) {
                    return $categoryIdMap[$category];
                }
                // 如果映射中没有，尝试从数据库查询
                $categoryInfo = Db::name('symptom_categories')
                    ->where('name_zh_cn', $category)
                    ->find();
                if ($categoryInfo) {
                    return $categoryInfo['id'];
                }
            }
        }

        // 2. 关键词语义匹配
        foreach ($this->symptomCategoryKeywords as $categoryName => $keywords) {
            foreach ($keywords as $keyword) {
                if (strpos($symptom, $keyword) !== false) {
                    if (isset($categoryIdMap[$categoryName])) {
                        return $categoryIdMap[$categoryName];
                    }
                    $categoryInfo = Db::name('symptom_categories')
                        ->where('name_zh_cn', $categoryName)
                        ->find();
                    if ($categoryInfo) {
                        return $categoryInfo['id'];
                    }
                }
            }
        }

        // 3. 兜底匹配：使用"其他症状"分类
        if (isset($categoryIdMap['其他症状'])) {
            return $categoryIdMap['其他症状'];
        }
        $otherCategory = Db::name('symptom_categories')
            ->where('name_zh_cn', '其他症状')
            ->find();

        return $otherCategory ? $otherCategory['id'] : null;
    }

    /**
     * 获取症状ID列表
     */
    private function getSymptomIds($symptoms)
    {
        $ids = [];
        if (!is_array($symptoms)) {
            return $ids;
        }

        foreach ($symptoms as $symptom) {
            if (!empty($symptom)) {
                $symptomInfo = Db::name('symptoms')
                    ->where('name_zh_cn', $symptom)
                    ->find();
                if ($symptomInfo) {
                    $ids[] = $symptomInfo['id'];
                }
            }
        }
        return $ids;
    }

    /**
     * 生成多语言翻译
     */
    private function generateTranslations($text)
    {
        // 如果在预定义翻译表中找到，直接使用
        if (isset($this->translations['en'][$text])) {
            return [
                'en' => $this->translations['en'][$text],
                'ja' => $this->translations['ja'][$text] ?? $this->smartTranslate($text, 'ja'),
                'be' => $this->translations['be'][$text] ?? $this->smartTranslate($text, 'be')
            ];
        }

        // 否则使用智能翻译
        return [
            'en' => $this->smartTranslate($text, 'en'),
            'ja' => $this->smartTranslate($text, 'ja'),
            'be' => $this->smartTranslate($text, 'be')
        ];
    }

    /**
     * 智能翻译方法
     */
    private function smartTranslate($text, $language)
    {
        // 根据语言调用对应的翻译方法
        switch ($language) {
            case 'en':
                return $this->translateToEnglish($text);
            case 'ja':
                return $this->translateToJapanese($text);
            case 'be':
                return $this->translateToBelarus($text);
            default:
                return $text;
        }
    }

    /**
     * 翻译为英语
     */
    private function translateToEnglish($text)
    {
        // 中医术语英语翻译
        $medicalTranslations = [
            // 症状类
            '头痛' => 'Headache',
            '头晕' => 'Dizziness',
            '眩晕' => 'Vertigo',
            '偏头痛' => 'Migraine',
            '发热' => 'Fever',
            '发烧' => 'Fever',
            '寒热' => 'Chills and Fever',
            '恶寒' => 'Aversion to Cold',
            '畏寒' => 'Fear of Cold',

            '咳嗽' => 'Cough',
            '咳痰' => 'Cough with Phlegm',
            '气喘' => 'Asthma',
            '气短' => 'Shortness of Breath',
            '胸闷' => 'Chest Tightness',
            '胸痛' => 'Chest Pain',
            '咽痛' => 'Sore Throat',
            '咽干' => 'Dry Throat',

            '腹痛' => 'Abdominal Pain',
            '胃痛' => 'Stomach Pain',
            '腹胀' => 'Abdominal Distention',
            '腹泻' => 'Diarrhea',
            '便秘' => 'Constipation',
            '便溏' => 'Loose Stool',
            '恶心' => 'Nausea',
            '呕吐' => 'Vomiting',
            '食欲不振' => 'Loss of Appetite',
            '口渴' => 'Thirst',
            '口干' => 'Dry Mouth',
            '口淡' => 'Bland Taste',

            '心悸' => 'Palpitation',
            '心慌' => 'Heart Flutter',
            '失眠' => 'Insomnia',
            '多梦' => 'Excessive Dreams',
            '健忘' => 'Forgetfulness',
            '烦躁' => 'Irritability',
            '抑郁' => 'Depression',
            '焦虑' => 'Anxiety',

            '疲劳' => 'Fatigue',
            '乏力' => 'Weakness',
            '神疲' => 'Mental Fatigue',
            '身痛' => 'Body Aches',
            '关节痛' => 'Joint Pain',
            '腰痛' => 'Lower Back Pain',

            '小便不利' => 'Dysuria',
            '尿频' => 'Frequent Urination',
            '水肿' => 'Edema',
            '浮肿' => 'Swelling',

            '月经不调' => 'Irregular Menstruation',
            '痛经' => 'Dysmenorrhea',
            '闭经' => 'Amenorrhea',

            // 部位类
            '头颈部症状' => 'Head & Neck Symptoms',
            '呼吸系统症状' => 'Respiratory Symptoms',
            '消化系统症状' => 'Digestive Symptoms',
            '循环系统症状' => 'Circulatory Symptoms',
            '神经系统症状' => 'Nervous System Symptoms',
            '泌尿系统症状' => 'Urinary System Symptoms',
            '妇科症状' => 'Gynecological Symptoms',
            '皮肤症状' => 'Skin Symptoms',
            '其他症状' => 'Other Symptoms',

            '头部' => 'Head',
            '颈部' => 'Neck',
            '胸部' => 'Chest',
            '腹部' => 'Abdomen',
            '背部' => 'Back',
            '腰部' => 'Lower Back',
            '四肢' => 'Limbs',

            // 方剂类
            '桂枝汤' => 'Cinnamon Twig Decoction',
            '小青龙汤' => 'Minor Blue Dragon Decoction',
            '银翘散' => 'Honeysuckle and Forsythia Powder',
            '麻黄汤' => 'Ephedra Decoction',
            '大青龙汤' => 'Major Blue Dragon Decoction',
            '五苓散' => 'Five-Ingredient Powder with Poria',
            '平胃散' => 'Stomach-Calming Powder',
            '六君子汤' => 'Six-Gentleman Decoction',
            '逍遥散' => 'Free and Easy Wanderer Powder',
            '补中益气汤' => 'Tonify the Middle and Augment the Qi Decoction',
        ];

        return $medicalTranslations[$text] ?? $text . ' (Chinese Medicine)';
    }

    /**
     * 翻译为日语
     */
    private function translateToJapanese($text)
    {
        $japaneseTranslations = [
            // 症状类
            '头痛' => '頭痛',
            '头晕' => 'めまい',
            '眩晕' => '眩暈',
            '偏头痛' => '偏頭痛',
            '发热' => '発熱',
            '发烧' => '発熱',
            '寒热' => '悪寒発熱',
            '恶寒' => '悪寒',
            '畏寒' => '寒がり',

            '咳嗽' => '咳',
            '咳痰' => '痰を伴う咳',
            '气喘' => '喘息',
            '气短' => '息切れ',
            '胸闷' => '胸苦しさ',
            '胸痛' => '胸痛',
            '咽痛' => '咽頭痛',
            '咽干' => '咽の乾燥',

            '腹痛' => '腹痛',
            '胃痛' => '胃痛',
            '腹胀' => '腹部膨満',
            '腹泻' => '下痢',
            '便秘' => '便秘',
            '便溏' => '軟便',
            '恶心' => '吐き気',
            '呕吐' => '嘔吐',
            '食欲不振' => '食欲不振',
            '口渴' => '口渇',
            '口干' => '口の乾燥',
            '口淡' => '口の中が淡白',

            '心悸' => '動悸',
            '心慌' => '心の動揺',
            '失眠' => '不眠症',
            '多梦' => '多夢',
            '健忘' => '健忘',
            '烦躁' => '煩燥',
            '抑郁' => '抑うつ',
            '焦虑' => '不安',

            '疲劳' => '疲労',
            '乏力' => '脱力',
            '神疲' => '精神疲労',
            '身痛' => '身体痛',
            '关节痛' => '関節痛',
            '腰痛' => '腰痛',

            '小便不利' => '排尿困難',
            '尿频' => '頻尿',
            '水肿' => '浮腫',
            '浮肿' => '浮腫',

            '月经不调' => '月経不順',
            '痛经' => '月経痛',
            '闭经' => '無月経',

            // 部位类
            '头颈部症状' => '頭頸部症状',
            '呼吸系统症状' => '呼吸器症状',
            '消化系统症状' => '消化器症状',
            '循环系统症状' => '循環器症状',
            '神经系统症状' => '神経系症状',
            '泌尿系统症状' => '泌尿器症状',
            '妇科症状' => '婦人科症状',
            '皮肤症状' => '皮膚症状',
            '其他症状' => 'その他の症状',

            '头部' => '頭部',
            '颈部' => '頸部',
            '胸部' => '胸部',
            '腹部' => '腹部',
            '背部' => '背部',
            '腰部' => '腰部',
            '四肢' => '四肢',

            // 方剂类
            '桂枝汤' => '桂枝湯',
            '小青龙汤' => '小青竜湯',
            '银翘散' => '銀翹散',
            '麻黄汤' => '麻黄湯',
            '大青龙汤' => '大青竜湯',
            '五苓散' => '五苓散',
            '平胃散' => '平胃散',
            '六君子汤' => '六君子湯',
            '逍遥散' => '逍遥散',
            '补中益气汤' => '補中益気湯',
        ];

        return $japaneseTranslations[$text] ?? $text . '（中医学）';
    }

    /**
     * 翻译为白俄罗斯语
     */
    private function translateToBelarus($text)
    {
        $belarusTranslations = [
            // 症状类
            '头痛' => 'Галаўны боль',
            '头晕' => 'Запаморкі',
            '眩晕' => 'Галавакружэнне',
            '偏头痛' => 'Мігрэнь',
            '发热' => 'Ліхаманка',
            '发烧' => 'Ліхаманка',
            '寒热' => 'Дрыжыкі і ліхаманка',
            '恶寒' => 'Непераносімасць холаду',
            '畏寒' => 'Страх холаду',

            '咳嗽' => 'Кашаль',
            '咳痰' => 'Кашаль з мокротай',
            '气喘' => 'Удушша',
            '气短' => 'Задышка',
            '胸闷' => 'Цяжкасць у грудзях',
            '胸痛' => 'Боль у грудзях',
            '咽痛' => 'Боль у горле',
            '咽干' => 'Сухасць у горле',

            '腹痛' => 'Боль у жываце',
            '胃痛' => 'Боль у страўніку',
            '腹胀' => 'Здзіманне жывата',
            '腹泻' => 'Панос',
            '便秘' => 'Закрэп',
            '便溏' => 'Мяккі стул',
            '恶心' => 'Млосць',
            '呕吐' => 'Вантота',
            '食欲不振' => 'Страта апетыту',
            '口渴' => 'Смага',
            '口干' => 'Сухасць у роце',
            '口淡' => 'Безгустоўнасць',

            '心悸' => 'Сэрцабіенне',
            '心慌' => 'Сэрцавае хваляванне',
            '失眠' => 'Бяссонніца',
            '多梦' => 'Шмат сноў',
            '健忘' => 'Забыўчасць',
            '烦躁' => 'Раздражняльнасць',
            '抑郁' => 'Дэпрэсія',
            '焦虑' => 'Трывожнасць',

            '疲劳' => 'Стомленасць',
            '乏力' => 'Слабасць',
            '神疲' => 'Псіхічная стомленасць',
            '身痛' => 'Боль у целе',
            '关节痛' => 'Боль у суставах',
            '腰痛' => 'Боль у спіне',

            '小便不利' => 'Парушэнне мачавыпускання',
            '尿频' => 'Частое мачавыпусканне',
            '水肿' => 'Набрыяк',
            '浮肿' => 'Набрыяк',

            '月经不调' => 'Парушэнне менструацыі',
            '痛经' => 'Баляючая менструацыя',
            '闭经' => 'Адсутнасць менструацыі',

            // 部位类
            '头颈部症状' => 'Сімптомы галавы і шыі',
            '呼吸系统症状' => 'Сімптомы дыхальнай сістэмы',
            '消化系统症状' => 'Сімптомы стрававальнай сістэмы',
            '循环系统症状' => 'Сімптомы кровазвярнення',
            '神经系统症状' => 'Сімптомы нервовай сістэмы',
            '泌尿系统症状' => 'Сімптомы мачаполавой сістэмы',
            '妇科症状' => 'Гінекалагічныя сімптомы',
            '皮肤症状' => 'Сімптомы скуры',
            '其他症状' => 'Іншыя сімптомы',

            '头部' => 'Галава',
            '颈部' => 'Шыя',
            '胸部' => 'Грудзі',
            '腹部' => 'Жывот',
            '背部' => 'Спіна',
            '腰部' => 'Паясніца',
            '四肢' => 'Канечнасці',

            // 方剂类
            '桂枝汤' => 'Адвар корніца карыцы',
            '小青龙汤' => 'Малы сіні дракон адвар',
            '银翘散' => 'Парашок жымолось і форзіцыя',
            '麻黄汤' => 'Адвар эфедры',
            '大青龙汤' => 'Вялікі сіні дракон адвар',
            '五苓散' => 'Парашок пяці кампанентаў з порыяй',
            '平胃散' => 'Парашок для супакаення страўніка',
            '六君子汤' => 'Адвар шасці шляхетных',
            '逍遥散' => 'Парашок вольнага вандроўніка',
            '补中益气汤' => 'Адвар для ўмацавання сярэдняга і павелічэння ці',
        ];

        return $belarusTranslations[$text] ?? $text . ' (кітайская медыцына)';
    }

    /**
     * 按分隔符分割并清理数据，确保UTF-8安全
     * 支持中文逗号（，）、英文逗号（,）、顿号（、）、中文分号（；）、英文分号（;）
     * 重要：不按空格分割，避免拆分完整的症状描述
     */
    private function splitByComma($text)
    {
        // 确保输入是字符串类型
        if ($text === null || $text === false) {
            return [];
        }

        $text = (string)$text;

        if (empty($text)) return [];

        // 首先记录原始文本用于调试
        $originalText = $text;
        Log::info("开始分割文本: '{$originalText}'");

        // 首先确保文本是UTF-8编码
        $text = $this->cleanCellValue($text);

        if (empty($text)) {
            Log::warning("文本清理后为空，跳过: '{$originalText}'");
            return [];
        }

        // 只按明确的分隔符分割：中文逗号、英文逗号、顿号、中文分号、英文分号
        // 绝对不要按空格分割！
        $text = preg_replace('/[，,、；;]/', '|DELIMITER|', $text);

        // 处理连续的分隔符
        $text = preg_replace('/\|DELIMITER\|\s*\|DELIMITER\|/', '|DELIMITER|', $text);

        // 去除分隔符前后的空格
        $text = preg_replace('/\s*\|DELIMITER\|\s*/', '|DELIMITER|', $text);

        // 去除首尾分隔符
        $text = trim($text, '|DELIMITER|');

        if (empty($text)) {
            Log::warning("文本处理后为空，跳过: '{$originalText}'");
            return [];
        }

        // 按分隔符分割
        $items = explode('|DELIMITER|', $text);
        $cleanItems = [];

        Log::info("分割得到 " . count($items) . " 个项目");

        foreach ($items as $index => $item) {
            $item = trim($item);

            // 跳过空值
            if (empty($item)) {
                continue;
            }

            // 跳过太短的项（少于2个字符通常是分割错误）
            $itemLength = mb_strlen($item, 'UTF-8');
            if ($itemLength < 2) {
                Log::warning("项目 {$index} 太短（{$itemLength}字），可能是分割错误，跳过: '{$item}'");
                continue;
            }

            // 检测可能的错误片段（比如单独的"下降"、"软"、"严重"等）
            $suspiciousFragments = ['下降', '软', '严重', '不安', '虚脱', '燥', '溏', '后仍', '但无', '且'];
            if ($itemLength <= 3 && in_array($item, $suspiciousFragments)) {
                Log::warning("检测到可能的错误片段，跳过: '{$item}'");
                continue;
            }

            // 基本检查：必须包含中文字符
            if (preg_match('/[\x{4e00}-\x{9fa5}]/u', $item)) {
                // 避免重复添加
                if (!in_array($item, $cleanItems)) {
                    $cleanItems[] = $item;
                    Log::info("接受项目 {$index}: '{$item}' (长度: " . mb_strlen($item, 'UTF-8') . ")");
                }
            } else {
                Log::warning("项目 {$index} 不包含中文，跳过: '{$item}'");
            }
        }

        // 去重并返回
        $cleanItems = array_unique($cleanItems);

        // 记录分割结果以便调试
        if (!empty($cleanItems)) {
            Log::info("分割完成，有效项目数: " . count($cleanItems));
        } else {
            Log::warning("分割完成，但没有有效项目，原文: '{$originalText}'");
        }

        return array_values($cleanItems);
    }

    /**
     * 检查是否为有效的中文医学术语
     */
    private function isValidChineseMedicalTerm($text)
    {
        // 检查长度 - 放宽限制
        $length = mb_strlen($text, 'UTF-8');
        if ($length < 1 || $length > 15) {
            Log::warning("文本长度不符合要求: '{$text}' (长度: {$length})");
            return false;
        }

        // 检查：必须包含中文字符（允许部分英文或数字）
        if (!preg_match('/[\x{4e00}-\x{9fa5}]/u', $text)) {
            Log::warning("文本不包含中文字符: '{$text}'");
            return false;
        }

        // 检查是否包含乱码 - 这是最重要的检查
        if ($this->isLikelyCorruptedText($text)) {
            Log::warning("文本包含乱码: '{$text}'");
            return false;
        }

        // 如果是纯中文且通过乱码检测，就接受
        if (preg_match('/^[\x{4e00}-\x{9fa5}]+$/u', $text)) {
            Log::info("接受纯中文文本: '{$text}'");
            return true;
        }

        // 如果包含中文+其他字符，检查是否合理
        if (preg_match('/^[\x{4e00}-\x{9fa5}a-zA-Z0-9\s\(\)（）]+$/u', $text)) {
            Log::info("接受混合文本: '{$text}'");
            return true;
        }

        Log::warning("文本包含不允许的字符: '{$text}'");
        return false;
    }

    /**
     * 从Excel文件读取数据
     */
    private function readExcelData()
    {
        // 直接使用绝对路径
        $excelPath = 'D:\phpstudy_pro\ai\intvale\ai_1\doc\sbb.xlsx';

        // 检查文件是否存在
        if (!file_exists($excelPath)) {
            throw new \Exception("Excel文件不存在: {$excelPath}");
        }

        // 读取Excel文件
        $spreadsheet = IOFactory::load($excelPath);

        // 调试：检查是否成功加载
        if (!$spreadsheet) {
            echo json_encode(['error' => 'Failed to load spreadsheet']);
            die();
        }

        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow(); // 获取总行数
        $highestColumn = $worksheet->getHighestColumn(); // 获取总列数

        Log::info("Excel文件读取成功，行数: {$highestRow}，列数: {$highestColumn}");

        $data = [];
        // 从第2行开始读取（跳过第1行标题）
        for ($row = 2; $row <= $highestRow; $row++) {
            $rowData = [];

            // 读取A、B、C、D列，使用多种方法确保数据正确
            try {
                // 尝试获取原始值，然后转换为字符串
                $cellA = $worksheet->getCell('A' . $row);
                $cellB = $worksheet->getCell('B' . $row);
                $cellC = $worksheet->getCell('C' . $row);
                $cellD = $worksheet->getCell('D' . $row);

                // 优先使用getCalculatedValue，失败则使用getValue
                $valueA = $this->safeGetCellValue($cellA);
                $valueB = $this->safeGetCellValue($cellB);
                $valueC = $this->safeGetCellValue($cellC);
                $valueD = $this->safeGetCellValue($cellD);

            } catch (\Exception $e) {
                // 如果获取失败，跳过这一行
                Log::error("读取第{$row}行数据失败: " . $e->getMessage());
                continue;
            }

            $rowData[0] = $this->cleanCellValue($valueA); // 汤药名称
            $rowData[1] = $this->cleanCellValue($valueB); // 主证状
            $rowData[2] = $this->cleanCellValue($valueC); // 兼症状
            $rowData[3] = $this->cleanCellValue($valueD); // 所属分类

            // 跳过空行（汤药名称为空的行）
            if (!empty($rowData[0])) {
                $data[] = $rowData;
            }
        }

        Log::info("从Excel读取到 " . count($data) . " 行有效数据");
        return $data;
    }

    /**
     * 安全获取单元格值
     */
    private function safeGetCellValue($cell)
    {
        try {
            // 首先尝试获取计算值
            $value = $cell->getCalculatedValue();
            if ($value !== null && !is_object($value)) {
                return (string)$value;
            }
        } catch (\Exception $e) {
            // 忽略计算错误
        }

        try {
            // 如果计算值失败，尝试获取原始值
            $value = $cell->getValue();
            if ($value !== null && !is_object($value)) {
                return (string)$value;
            }
        } catch (\Exception $e) {
            // 忽略获取错误
        }

        try {
            // 最后尝试获取格式化值
            $value = $cell->getFormattedValue();
            if ($value !== null && !is_object($value)) {
                return (string)$value;
            }
        } catch (\Exception $e) {
            // 忽略格式化错误
        }

        // 如果所有方法都失败，返回空字符串
        return '';
    }

    /**
     * 清理单元格值并确保UTF-8编码
     */
    private function cleanCellValue($value)
    {
        if ($value === null || $value === false) {
            return '';
        }

        // 转换为字符串
        $value = (string)$value;

        // 去除首尾空格
        $value = trim($value);

        if (empty($value)) {
            return '';
        }

        // 检测并修复编码问题
        $originalValue = $value;

        // 特殊乱码修复映射表
        $corruptionMap = [
            '鑵拌' => '胸部',
            '浜烘劅瑙夎櫄' => '人感觉虚',
            '撮毦鍙' => '咳嗽', // 根据上下文推测
            '韬轰笅' => '躺下', // 根据上下文推测
            // 常见乱码映射
            '澶撮儴' => '头部',
            '鑳搁儴' => '胸部',
            '鑵归儴' => '腹部',
            '鑳岄儴' => '背部',
            '鍝枠' => '哮喘',
            '澶寸棝' => '头痛',
            '澶存檿' => '头晕',
            '鍙戠儹' => '发热',
            '鍜冲椊' => '咳嗽',
            '鑳搁椃' => '胸闷',
            '鑵圭棝' => '腹痛',
            '渚跨' => '便秘',
            '鑵规郴' => '腹泻',
            '蹇冩倲' => '心悸',
            '澶辩湢' => '失眠',
            '鐤插姵' => '疲劳',
            '椋熸鑲剁噲' => '食欲不振',
            '鎭跺績' => '恶心',
            '鍛曞悙' => '呕吐',
            '鍙f复' => '口渴',
            '鍙f贰' => '口淡',
            '浜忔皵' => '乏气',
            '姘斿枠' => '气喘',
            '姘旂煭' => '气短',
            '鐑﹁簛' => '烦躁',
            '鎶戦儊' => '抑郁',
            '鐒﹁檻' => '焦虑',
            '鎯婃倲' => '惊悸',
            // 可以继续添加其他已知的乱码映射
        ];

        // 首先检查是否是已知的乱码
        if (isset($corruptionMap[$value])) {
            Log::info("修复已知乱码: '{$value}' -> '{$corruptionMap[$value]}'");
            return $corruptionMap[$value];
        }

        // 🚨 临时关闭乱码检测，先看看能读取到什么
        // if ($this->isLikelyCorruptedText($value)) {
        //     Log::warning("检测到乱码数据，直接跳过: '{$value}'");
        //     return ''; // 返回空字符串，这样后续处理会自动过滤掉
        // }

        // 尝试智能编码检测和修复
        $value = $this->intelligentEncodingFix($value);

        // 再次检测修复后的结果 - 临时关闭
        // if ($this->isLikelyCorruptedText($value)) {
        //     Log::warning("修复后仍然是乱码，跳过: '{$originalValue}' -> '{$value}'");
        //     return ''; // 直接跳过
        // }

        // 移除不可见字符和控制字符（保留中文字符）
        $value = preg_replace('/[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]/', '', $value);

        // 移除零宽字符和其他隐藏字符
        $value = preg_replace('/[\x{200B}-\x{200D}\x{FEFF}]/u', '', $value);

        return trim($value);
    }

    /**
     * 智能编码检测和修复
     */
    private function intelligentEncodingFix($value)
    {
        $originalValue = $value;

        // 如果是有效的UTF-8，直接返回
        if (mb_check_encoding($value, 'UTF-8')) {
            // 但可能是错误编码导致的"伪UTF-8"，需要进一步检查
            if ($this->isLikelyCorruptedText($value)) {
                // 尝试反向编码修复
                $fixed = $this->tryFixCorruptedEncoding($value);
                if ($fixed && $fixed !== $value) {
                    Log::info("修复编码: '{$value}' -> '{$fixed}'");
                    return $fixed;
                }
            }
            return $value;
        }

        // 如果不是UTF-8，尝试多种编码转换
        $encodings = ['GBK', 'GB2312', 'GB18030', 'BIG5', 'ISO-8859-1', 'Windows-1252'];
        foreach ($encodings as $encoding) {
            $converted = @mb_convert_encoding($value, 'UTF-8', $encoding);
            if ($converted && mb_check_encoding($converted, 'UTF-8') && !$this->isLikelyCorruptedText($converted)) {
                Log::info("成功转换编码 {$encoding}: '{$value}' -> '{$converted}'");
                return $converted;
            }
        }

        // 如果所有转换都失败，使用原始值
        return $originalValue;
    }

    /**
     * 检测是否可能是损坏的文本
     */
    private function isLikelyCorruptedText($text)
    {
        // 更严格的乱码检测模式
        $suspiciousPatterns = [
            // 常见乱码字符组合
            '/[鑵拰銆鍙毦撮韬]/u',          // 已知乱码字符
            '/[锟]{2,}/u',                  // 连续的锟字符
            '/[涔愰]/u',                    // 其他常见乱码
            '/[澶村彂鑳搁椃鑵归]/u',             // 常见乱码模式
            '/[鍜冲椊鐤插姵]/u',              // 医学术语乱码
            '/[鎭跺績鍛曞悙]/u',              // 症状乱码
            '/[鐑﹁簛鎶戦]/u',               // 情绪乱码
            '/[鍝枠澶辩湢]/u',               // 病症乱码
            '/[脙茠脗鈥]/u',                 // UTF-8双重编码乱码
            '/[閸慇]/u',                   // 其他常见乱码
            '/[姘旇剱瀛愯偤]/u',               // 器官乱码
            '/[璐ヨ偞娲]/u',                 // 动作乱码
            '/[妤￠]/u',                    // 建筑乱码
            '/[瀛愬]/u',                    // 人体乱码
            '/[闊宠皟]/u',                  // 声音乱码
            '/[鍦伴毦]/u',                  // 地理乱码
            '/[娑查緞]/u',                  // 液体乱码

            // 检测看起来像是编码错误的模式（特别是你提到的乱码）
            '/鑴夋悘铏氬/u',                  // 你提到的具体乱码
            '/鑴[夋悘]/u',                   // 鑴开头的乱码模式
            '/[悘铏氬]/u',                   // 相关乱码字符
            '/[夋悘铏]/u',                   // 单个乱码字符检测
            '/[鍏冲厓]/u',                   // 其他类似模式
            '/[閲嶅畨]/u',                   // 重复类型
            '/[鎴愰暱]/u',                   // 成长类型
            '/[鎶椾綋]/u',                   // 抗体类型
            '/[鐥囩姸]/u',                   // 症状类型乱码
            '/[鑲濊儐]/u',                   // 器官类型乱码

            // 检测字符编码异常
            '/[\x{E000}-\x{F8FF}]/u',       // 私用区字符
            '/[\x{FFF0}-\x{FFFF}]/u',       // 特殊用途字符
        ];

        foreach ($suspiciousPatterns as $pattern) {
            if (preg_match($pattern, $text)) {
                Log::warning("检测到乱码模式: '{$text}' 匹配 {$pattern}");
                return true;
            }
        }

        // 检查是否主要由非常见中文字符组成
        if ($this->isNonCommonChinese($text)) {
            Log::warning("检测到非常见中文字符: '{$text}'");
            return true;
        }

        // 检查字符分布异常（大量生僻字符可能是乱码）
        $rareCharCount = 0;
        $totalChars = mb_strlen($text, 'UTF-8');

        if ($totalChars > 0) {
            // 统计生僻字符（Unicode编码较高的字符）
            for ($i = 0; $i < $totalChars; $i++) {
                $char = mb_substr($text, $i, 1, 'UTF-8');
                $ord = mb_ord($char, 'UTF-8');
                // 如果字符的Unicode编码过高，可能是乱码
                if ($ord > 0x9FFF || $ord < 0x4E00) {
                    $rareCharCount++;
                }
            }

            // 如果生僻字符比例过高，可能是乱码
            if ($rareCharCount / $totalChars > 0.3) {
                Log::warning("检测到异常字符分布: '{$text}' (生僻字符比例: " . round($rareCharCount / $totalChars * 100) . "%)");
                return true;
            }
        }

        return false;
    }

    /**
     * 检测是否为非常见中文字符
     */
    private function isNonCommonChinese($text)
    {
        $totalChars = mb_strlen($text, 'UTF-8');
        if ($totalChars == 0) return false;

        $commonChineseCount = 0;

        // 常见中文医学术语字符
        $commonMedicalChars = [
            '头', '痛', '晕', '发', '热', '咳', '嗽', '胸', '闷', '腹', '泻', '便', '秘',
            '心', '悸', '失', '眠', '疲', '劳', '食', '欲', '不', '振', '恶', '心', '呕', '吐',
            '口', '渴', '干', '身', '汗', '出', '气', '短', '喘', '烦', '躁', '抑', '郁',
            '焦', '虑', '小', '便', '利', '水', '肿', '月', '经', '调', '痛', '闭', '症', '状',
            '系', '统', '神', '循', '环', '消', '化', '呼', '吸', '泌', '尿', '妇', '科', '皮', '肤',
            '汤', '药', '方', '剂', '治', '疗', '功', '效', '主', '证', '兼', '桂', '枝', '麻', '黄',
            '银', '翘', '散', '青', '龙', '五', '苓', '平', '胃', '君', '子', '逍', '遥', '补', '中',
            '益', '气', '部', '颈', '其', '他', '分', '类', '关', '节', '腰', '背', '四', '肢'
        ];

        for ($i = 0; $i < $totalChars; $i++) {
            $char = mb_substr($text, $i, 1, 'UTF-8');
            if (in_array($char, $commonMedicalChars)) {
                $commonChineseCount++;
            }
        }

        // 如果常见医学中文字符比例太低，可能是乱码
        $commonRatio = $commonChineseCount / $totalChars;
        return $commonRatio < 0.3; // 30%以下认为可能是乱码
    }

    /**
     * 尝试修复损坏的编码
     */
    private function tryFixCorruptedEncoding($text)
    {
        // 记录原始文本用于调试
        Log::info("尝试修复乱码: '{$text}'");

        // 尝试各种编码修复策略
        $attempts = [
            // 策略1: UTF-8 -> GBK -> UTF-8 的修复
            function($t) {
                return @mb_convert_encoding(@mb_convert_encoding($t, 'GBK', 'UTF-8'), 'UTF-8', 'GBK');
            },

            // 策略2: UTF-8 -> ISO-8859-1 -> GBK -> UTF-8 的修复
            function($t) {
                return @mb_convert_encoding(@mb_convert_encoding(@mb_convert_encoding($t, 'ISO-8859-1', 'UTF-8'), 'GBK', 'ISO-8859-1'), 'UTF-8', 'GBK');
            },

            // 策略3: Windows-1252 -> UTF-8 的修复
            function($t) {
                return @mb_convert_encoding($t, 'UTF-8', 'Windows-1252');
            },

            // 策略4: Big5 编码修复
            function($t) {
                return @mb_convert_encoding(@mb_convert_encoding($t, 'BIG5', 'UTF-8'), 'UTF-8', 'BIG5');
            },

            // 策略5: GB18030 编码修复
            function($t) {
                return @mb_convert_encoding(@mb_convert_encoding($t, 'GB18030', 'UTF-8'), 'UTF-8', 'GB18030');
            },

            // 策略6: 三重编码修复 UTF-8 -> GBK -> ISO-8859-1 -> UTF-8
            function($t) {
                $step1 = @mb_convert_encoding($t, 'GBK', 'UTF-8');
                $step2 = @mb_convert_encoding($step1, 'ISO-8859-1', 'GBK');
                return @mb_convert_encoding($step2, 'UTF-8', 'ISO-8859-1');
            },

            // 策略7: 反向的三重编码修复
            function($t) {
                $step1 = @mb_convert_encoding($t, 'ISO-8859-1', 'UTF-8');
                $step2 = @mb_convert_encoding($step1, 'GBK', 'ISO-8859-1');
                return @mb_convert_encoding($step2, 'UTF-8', 'GBK');
            },
        ];

        foreach ($attempts as $index => $attempt) {
            $result = $attempt($text);
            if ($result && $result !== $text) {
                // 检查修复结果的质量
                if ($this->isValidChineseText($result) && !$this->isLikelyCorruptedText($result)) {
                    Log::info("策略" . ($index + 1) . "修复成功: '{$text}' -> '{$result}'");
                    return $result;
                }
            }
        }

        // 如果所有策略都失败，尝试模糊匹配
        $fuzzyResult = $this->tryFuzzyMatch($text);
        if ($fuzzyResult) {
            Log::info("模糊匹配修复: '{$text}' -> '{$fuzzyResult}'");
            return $fuzzyResult;
        }

        Log::warning("无法修复乱码: '{$text}'");
        return $text;
    }

    /**
     * 检查是否是有效的中文文本
     */
    private function isValidChineseText($text)
    {
        // 检查是否主要包含中文字符
        $chineseCharCount = 0;
        $totalChars = mb_strlen($text, 'UTF-8');

        if ($totalChars == 0) return false;

        for ($i = 0; $i < $totalChars; $i++) {
            $char = mb_substr($text, $i, 1, 'UTF-8');
            $ord = mb_ord($char, 'UTF-8');
            // 检查是否是中文字符范围
            if (($ord >= 0x4e00 && $ord <= 0x9fa5) ||
                ($ord >= 0x3400 && $ord <= 0x4dbf) ||
                ($ord >= 0x20000 && $ord <= 0x2a6df)) {
                $chineseCharCount++;
            }
        }

        // 如果中文字符比例足够高，认为是有效的中文文本
        return ($chineseCharCount / $totalChars) > 0.6;
    }

    /**
     * 尝试模糊匹配已知词汇
     */
    private function tryFuzzyMatch($text)
    {
        // 常见医学术语的模糊匹配
        $commonTerms = [
            '头痛', '头晕', '发热', '咳嗽', '胸闷', '腹痛', '便秘', '腹泻',
            '心悸', '失眠', '疲劳', '食欲不振', '恶心', '呕吐', '口渴', '口淡',
            '乏气', '气喘', '气短', '烦躁', '抑郁', '焦虑', '惊悸', '胸部',
            '腹部', '头部', '背部', '躺下', '坐起', '站立', '行走'
        ];

        $textLength = mb_strlen($text, 'UTF-8');

        foreach ($commonTerms as $term) {
            $termLength = mb_strlen($term, 'UTF-8');
            // 如果长度相近，可能是该词的乱码
            if (abs($textLength - $termLength) <= 1) {
                // 简单的相似度检查（可以改进）
                $similarity = $this->calculateSimilarity($text, $term);
                if ($similarity > 0.3) { // 相似度阈值
                    return $term;
                }
            }
        }

        return null;
    }

    /**
     * 计算两个字符串的相似度（简化版）
     */
    private function calculateSimilarity($str1, $str2)
    {
        $len1 = mb_strlen($str1, 'UTF-8');
        $len2 = mb_strlen($str2, 'UTF-8');

        if ($len1 == 0 && $len2 == 0) return 1;
        if ($len1 == 0 || $len2 == 0) return 0;

        // 简单的字符重叠度计算
        $common = 0;
        for ($i = 0; $i < min($len1, $len2); $i++) {
            $char1 = mb_substr($str1, $i, 1, 'UTF-8');
            $char2 = mb_substr($str2, $i, 1, 'UTF-8');
            if ($char1 === $char2) {
                $common++;
            }
        }

        return $common / max($len1, $len2);
    }

    /**
     * 获取示例数据（作为备用）
     */
    private function getSampleData()
    {
        return [
            // A列：汤药名称, B列：主证状, C列：兼症状, D列：所属分类
            ['桂枝汤', '头痛,恶寒', '发热,汗出', '呼吸系统症状,头颈部症状'],
            ['小青龙汤', '咳嗽,气喘', '恶寒,无汗', '呼吸系统症状'],
            ['银翘散', '发热,头痛', '咽痛,口渴', '头颈部症状,呼吸系统症状'],
            ['麻黄汤', '恶寒,无汗', '头痛,身痛', '呼吸系统症状,头颈部症状'],
            ['大青龙汤', '恶寒,发热', '烦躁,口渴', '呼吸系统症状'],
            ['五苓散', '小便不利,水肿', '头晕,恶心', '循环系统症状,消化系统症状'],
            ['平胃散', '腹胀,食欲不振', '恶心,口淡', '消化系统症状'],
            ['六君子汤', '食欲不振,腹胀', '疲劳,便溏', '消化系统症状'],
            ['逍遥散', '胸胁胀痛,抑郁', '月经不调,头晕', '神经系统症状,妇科症状'],
            ['补中益气汤', '疲劳,气短', '食欲不振,腹泻', '消化系统症状']
        ];
    }

    /**
     * 简化版数据处理 - 跳过所有过滤
     */
    public function simpleProcessExcelData()
    {
        // 设置响应头确保UTF-8编码
        header('Content-Type: application/json; charset=utf-8');

        try {
            // 从Excel文件读取数据
            $excelData = $this->readExcelData();

            if (empty($excelData)) {
                return json([
                    'code' => 400,
                    'msg' => 'Excel数据为空',
                    'data' => null
                ]);
            }

            $result = [
                'categories_processed' => 0,
                'symptoms_processed' => 0,
                'formulas_processed' => 0,
                'errors' => [],
                'data_count' => count($excelData),
                'raw_excel_data' => $excelData,
                'processed_data' => []
            ];

            Log::info("简化版处理Excel数据，共 " . count($excelData) . " 行");

            // 简化处理：直接提取数据，不做复杂处理
            foreach ($excelData as $index => $row) {
                $processed = [
                    'row_index' => $index + 1,
                    'formula_name' => $row[0] ?? '',
                    'main_symptoms' => $row[1] ?? '',
                    'side_symptoms' => $row[2] ?? '',
                    'categories' => $row[3] ?? '',
                    'formula_name_length' => mb_strlen($row[0] ?? '', 'UTF-8'),
                    'main_symptoms_length' => mb_strlen($row[1] ?? '', 'UTF-8'),
                    'side_symptoms_length' => mb_strlen($row[2] ?? '', 'UTF-8'),
                    'categories_length' => mb_strlen($row[3] ?? '', 'UTF-8')
                ];
                $result['processed_data'][] = $processed;
            }

            return json([
                'code' => 200,
                'msg' => '简化版数据处理完成',
                'data' => $result
            ]);

        } catch (\Exception $e) {
            Log::error('简化版Excel数据处理失败：' . $e->getMessage());

            return json([
                'code' => 500,
                'msg' => '简化版Excel数据处理失败：' . $e->getMessage(),
                'data' => null
            ]);
        }
    }

    /**
     * 真正插入分类和症状数据（非调试模式）
     */
    public function insertCategoriesAndSymptoms()
    {
        header('Content-Type: application/json; charset=utf-8');

        try {
            // 从Excel文件读取数据
            $excelData = $this->readExcelData();

            if (empty($excelData)) {
                return json([
                    'code' => 400,
                    'msg' => 'Excel数据为空',
                    'data' => null
                ]);
            }

            $result = [
                'categories_inserted' => 0,
                'categories_skipped' => 0,
                'symptoms_inserted' => 0,
                'symptoms_skipped' => 0,
                'errors' => [],
                'data_count' => count($excelData)
            ];

            Log::info("开始插入分类和症状数据，共 " . count($excelData) . " 行");

            // 使用事务确保数据一致性
            Db::startTrans();
            try {
                // 阶段1：处理并插入分类数据（非调试模式）
                $categoriesResult = $this->processCategories($excelData, false);
                $result['categories_inserted'] = isset($categoriesResult['processed']) ? $categoriesResult['processed'] : 0;
                $result['categories_skipped'] = isset($categoriesResult['skipped']) ? $categoriesResult['skipped'] : 0;

                // 获取分类ID映射
                $categoryIdMap = isset($categoriesResult['category_map']) ? $categoriesResult['category_map'] : [];

                // 如果没有从processCategories获取到映射，重新构建
                if (empty($categoryIdMap)) {
                    $categories = Db::name('symptom_categories')->select();
                    foreach ($categories as $cat) {
                        $categoryIdMap[$cat['name_zh_cn']] = $cat['id'];
                    }
                }

                // 阶段2：处理并插入症状数据（非调试模式）
                $symptomsResult = $this->processSymptoms($excelData, false, $categoryIdMap);
                $result['symptoms_inserted'] = isset($symptomsResult['processed']) ? $symptomsResult['processed'] : 0;
                $result['symptoms_skipped'] = isset($symptomsResult['skipped']) ? $symptomsResult['skipped'] : 0;

                // 合并错误信息
                if (isset($categoriesResult['errors']) && is_array($categoriesResult['errors'])) {
                    $result['errors'] = array_merge($result['errors'], $categoriesResult['errors']);
                }
                if (isset($symptomsResult['errors']) && is_array($symptomsResult['errors'])) {
                    $result['errors'] = array_merge($result['errors'], $symptomsResult['errors']);
                }

                // 提交事务
                Db::commit();

                Log::info("分类和症状数据插入完成", $result);

                // 获取当前数据库统计
                $result['current_stats'] = [
                    'total_categories' => Db::name('symptom_categories')->count(),
                    'total_symptoms' => Db::name('symptoms')->count()
                ];

                return json([
                    'code' => 200,
                    'msg' => '分类和症状数据插入成功！',
                    'data' => $result
                ]);

            } catch (\Exception $e) {
                // 回滚事务
                Db::rollback();
                Log::error("数据插入失败，事务回滚: " . $e->getMessage());

                return json([
                    'code' => 500,
                    'msg' => '数据插入失败，已回滚：' . $e->getMessage(),
                    'data' => null
                ]);
            }

        } catch (\Exception $e) {
            Log::error('分类和症状数据插入失败：' . $e->getMessage());

            return json([
                'code' => 500,
                'msg' => '处理失败：' . $e->getMessage(),
                'data' => null
            ]);
        }
    }

    /**
     * 真正插入汤药数据（非调试模式）
     */
    public function insertFormulas()
    {
        header('Content-Type: application/json; charset=utf-8');

        try {
            // 先检查是否有症状数据
            $symptomsCount = Db::name('symptoms')->count();
            if ($symptomsCount == 0) {
                return json([
                    'code' => 400,
                    'msg' => '症状表为空，请先插入分类和症状数据！',
                    'data' => null
                ]);
            }

            // 从Excel文件读取数据
            $excelData = $this->readExcelData();

            if (empty($excelData)) {
                return json([
                    'code' => 400,
                    'msg' => 'Excel数据为空',
                    'data' => null
                ]);
            }

            $result = [
                'formulas_inserted' => 0,
                'formulas_skipped' => 0,
                'symptoms_not_found' => [],
                'errors' => [],
                'data_count' => count($excelData)
            ];

            Log::info("开始插入汤药数据，共 " . count($excelData) . " 行");

            // 使用事务确保数据一致性
            Db::startTrans();
            try {
                // 处理并插入汤药数据（非调试模式）
                $formulasResult = $this->processFormulas($excelData, false);
                $result['formulas_inserted'] = isset($formulasResult['processed']) ? $formulasResult['processed'] : 0;
                $result['formulas_skipped'] = isset($formulasResult['skipped']) ? $formulasResult['skipped'] : 0;

                // 合并错误信息
                if (isset($formulasResult['errors']) && is_array($formulasResult['errors'])) {
                    $result['errors'] = array_merge($result['errors'], $formulasResult['errors']);
                }

                // 检查未找到的症状
                $allSymptoms = [];
                foreach ($excelData as $row) {
                    $mainSymptoms = $this->splitByComma($row[1] ?? '');
                    $sideSymptoms = $this->splitByComma($row[2] ?? '');
                    $allSymptoms = array_merge($allSymptoms, $mainSymptoms, $sideSymptoms);
                }
                $allSymptoms = array_unique(array_filter($allSymptoms));

                foreach ($allSymptoms as $symptom) {
                    $exists = Db::name('symptoms')->where('name_zh_cn', $symptom)->find();
                    if (!$exists) {
                        $result['symptoms_not_found'][] = $symptom;
                    }
                }

                // 提交事务
                Db::commit();

                Log::info("汤药数据插入完成", $result);

                // 获取当前数据库统计
                $result['current_stats'] = [
                    'total_categories' => Db::name('symptom_categories')->count(),
                    'total_symptoms' => Db::name('symptoms')->count(),
                    'total_formulas' => Db::name('formulas')->count()
                ];

                // 获取几条汤药样本展示
                $result['formula_samples'] = Db::name('formulas')
                    ->field('id, name_zh_cn, main_symptom_ids, side_symptom_ids')
                    ->limit(5)
                    ->order('id desc')
                    ->select();

                return json([
                    'code' => 200,
                    'msg' => '汤药数据插入成功！',
                    'data' => $result
                ]);

            } catch (\Exception $e) {
                // 回滚事务
                Db::rollback();
                Log::error("汤药数据插入失败，事务回滚: " . $e->getMessage());

                return json([
                    'code' => 500,
                    'msg' => '汤药数据插入失败，已回滚：' . $e->getMessage(),
                    'data' => null
                ]);
            }

        } catch (\Exception $e) {
            Log::error('汤药数据插入失败：' . $e->getMessage());

            return json([
                'code' => 500,
                'msg' => '处理失败：' . $e->getMessage(),
                'data' => null
            ]);
        }
    }

    /**
     * 一键处理所有数据（分类、症状、汤药）
     */
    public function processAllData()
    {
        header('Content-Type: application/json; charset=utf-8');

        try {
            // 从Excel文件读取数据
            $excelData = $this->readExcelData();

            if (empty($excelData)) {
                return json([
                    'code' => 400,
                    'msg' => 'Excel数据为空',
                    'data' => null
                ]);
            }

            $result = [
                'categories_inserted' => 0,
                'categories_skipped' => 0,
                'symptoms_inserted' => 0,
                'symptoms_skipped' => 0,
                'formulas_inserted' => 0,
                'formulas_skipped' => 0,
                'errors' => [],
                'data_count' => count($excelData)
            ];

            Log::info("开始处理所有数据，共 " . count($excelData) . " 行");

            // 使用事务确保数据一致性
            Db::startTrans();
            try {
                // 阶段1：处理分类
                $categoriesResult = $this->processCategories($excelData, false);
                $result['categories_inserted'] = $categoriesResult['processed'] ?? 0;
                $result['categories_skipped'] = $categoriesResult['skipped'] ?? 0;
                $categoryIdMap = $categoriesResult['category_map'] ?? [];

                // 阶段2：处理症状
                if (empty($categoryIdMap)) {
                    $categories = Db::name('symptom_categories')->select();
                    foreach ($categories as $cat) {
                        $categoryIdMap[$cat['name_zh_cn']] = $cat['id'];
                    }
                }
                $symptomsResult = $this->processSymptoms($excelData, false, $categoryIdMap);
                $result['symptoms_inserted'] = $symptomsResult['processed'] ?? 0;
                $result['symptoms_skipped'] = $symptomsResult['skipped'] ?? 0;

                // 阶段3：处理汤药
                $formulasResult = $this->processFormulas($excelData, false);
                $result['formulas_inserted'] = $formulasResult['processed'] ?? 0;
                $result['formulas_skipped'] = $formulasResult['skipped'] ?? 0;

                // 合并错误
                if (isset($categoriesResult['errors'])) {
                    $result['errors'] = array_merge($result['errors'], $categoriesResult['errors']);
                }
                if (isset($symptomsResult['errors'])) {
                    $result['errors'] = array_merge($result['errors'], $symptomsResult['errors']);
                }
                if (isset($formulasResult['errors'])) {
                    $result['errors'] = array_merge($result['errors'], $formulasResult['errors']);
                }

                // 提交事务
                Db::commit();

                Log::info("所有数据处理完成", $result);

                // 获取当前数据库统计
                $result['current_stats'] = [
                    'total_categories' => Db::name('symptom_categories')->count(),
                    'total_symptoms' => Db::name('symptoms')->count(),
                    'total_formulas' => Db::name('formulas')->count()
                ];

                return json([
                    'code' => 200,
                    'msg' => '所有数据处理成功！',
                    'data' => $result
                ]);

            } catch (\Exception $e) {
                // 回滚事务
                Db::rollback();
                Log::error("数据处理失败，事务回滚: " . $e->getMessage());

                return json([
                    'code' => 500,
                    'msg' => '数据处理失败，已回滚：' . $e->getMessage(),
                    'data' => null
                ]);
            }

        } catch (\Exception $e) {
            Log::error('数据处理失败：' . $e->getMessage());

            return json([
                'code' => 500,
                'msg' => '处理失败：' . $e->getMessage(),
                'data' => null
            ]);
        }
    }

    /**
     * 检查数据库中的数据情况
     */
    public function checkDatabaseStatus()
    {
        header('Content-Type: application/json; charset=utf-8');

        try {
            // 检查各表的数据量
            $categoriesCount = Db::name('symptom_categories')->count();
            $symptomsCount = Db::name('symptoms')->count();
            $formulasCount = Db::name('formulas')->count();

            // 获取前5条数据示例
            $categoriesSample = Db::name('symptom_categories')->limit(5)->select();
            $symptomsSample = Db::name('symptoms')->limit(5)->select();
            $formulasSample = Db::name('formulas')->limit(5)->select();

            return json([
                'code' => 200,
                'msg' => '数据库状态检查完成',
                'data' => [
                    'summary' => [
                        'categories_count' => $categoriesCount,
                        'symptoms_count' => $symptomsCount,
                        'formulas_count' => $formulasCount
                    ],
                    'samples' => [
                        'categories' => $categoriesSample,
                        'symptoms' => $symptomsSample,
                        'formulas' => $formulasSample
                    ]
                ]
            ]);

        } catch (\Exception $e) {
            return json([
                'code' => 500,
                'msg' => '检查失败：' . $e->getMessage()
            ]);
        }
    }

    /**
     * 测试分割逻辑是否正确
     */
    public function testSplitLogic()
    {
        header('Content-Type: application/json; charset=utf-8');

        // 测试用例
        $testCases = [
            '耳朵听力下降；头晕' => ['耳朵听力下降', '头晕'],
            '烧退后仍感觉疲劳，食欲不振' => ['烧退后仍感觉疲劳', '食欲不振'],
            '腹痛、腹胀；便秘' => ['腹痛', '腹胀', '便秘'],
            '心悸 胸闷 气短' => ['心悸 胸闷 气短'], // 空格不应该分割
            '发热；咳嗽、头痛，乏力' => ['发热', '咳嗽', '头痛', '乏力']
        ];

        $results = [];
        foreach ($testCases as $input => $expected) {
            $actual = $this->splitByComma($input);
            $results[] = [
                'input' => $input,
                'expected' => $expected,
                'actual' => $actual,
                'passed' => $actual == $expected
            ];
        }

        // 实际从Excel读取几行数据测试
        try {
            $excelData = $this->readExcelData();
            $sampleResults = [];

            // 测试前5行数据
            for ($i = 0; $i < min(5, count($excelData)); $i++) {
                $row = $excelData[$i];
                $sampleResults[] = [
                    'row' => $i + 2, // Excel行号
                    'B_original' => $row[1] ?? '',
                    'B_split' => $this->splitByComma($row[1] ?? ''),
                    'C_original' => $row[2] ?? '',
                    'C_split' => $this->splitByComma($row[2] ?? '')
                ];
            }

            return json([
                'code' => 200,
                'msg' => '分割逻辑测试完成',
                'test_cases' => $results,
                'excel_samples' => $sampleResults
            ]);

        } catch (\Exception $e) {
            return json([
                'code' => 500,
                'msg' => '测试失败：' . $e->getMessage(),
                'test_cases' => $results
            ]);
        }
    }

    /**
     * 调试：直接读取Excel原始数据
     */
    public function debugExcelData()
    {
        // 设置响应头确保UTF-8编码
        header('Content-Type: application/json; charset=utf-8');

        try {
            // 直接读取Excel原始数据，不做任何处理
            $excelPath = 'D:\phpstudy_pro\ai\intvale\ai_1\doc\sbb.xlsx';

            if (!file_exists($excelPath)) {
                return json([
                    'code' => 400,
                    'msg' => "Excel文件不存在: {$excelPath}",
                    'data' => null
                ]);
            }

            $spreadsheet = IOFactory::load($excelPath);
            $worksheet = $spreadsheet->getActiveSheet();
            $highestRow = $worksheet->getHighestRow();

            $rawData = [];
            // 只读取前10行做调试
            for ($row = 1; $row <= min(10, $highestRow); $row++) {
                $rowData = [];
                for ($col = 'A'; $col <= 'D'; $col++) {
                    $cell = $worksheet->getCell($col . $row);
                    $rawValue = $cell->getValue();
                    $calculatedValue = $cell->getCalculatedValue();
                    $formattedValue = $cell->getFormattedValue();

                    $rowData[$col] = [
                        'raw' => $rawValue,
                        'calculated' => $calculatedValue,
                        'formatted' => $formattedValue,
                        'raw_type' => gettype($rawValue),
                        'is_string' => is_string($rawValue),
                        'mb_detect' => mb_detect_encoding($rawValue),
                        'strlen' => strlen((string)$rawValue),
                        'mb_strlen' => mb_strlen((string)$rawValue, 'UTF-8')
                    ];
                }
                $rawData["第{$row}行"] = $rowData;
            }

            return json([
                'code' => 200,
                'msg' => 'Excel原始数据读取成功',
                'data' => [
                    'file_path' => $excelPath,
                    'total_rows' => $highestRow,
                    'raw_data' => $rawData
                ]
            ]);

        } catch (\Exception $e) {
            return json([
                'code' => 500,
                'msg' => 'Excel原始数据读取失败：' . $e->getMessage(),
                'data' => null
            ]);
        }
    }

    /**
     * 获取处理统计信息
     */
    public function getStats()
    {
        try {
            $stats = [
                'categories_count' => Db::name('symptom_categories')->count(),
                'symptoms_count' => Db::name('symptoms')->count(),
                'formulas_count' => Db::name('formulas')->count()
            ];

            return json([
                'code' => 200,
                'msg' => 'success',
                'data' => $stats
            ]);

        } catch (\Exception $e) {
            return json([
                'code' => 500,
                'msg' => '获取统计信息失败：' . $e->getMessage(),
                'data' => null
            ]);
        }
    }

    /**
     * 获取数据库中的实际数据
     */
    public function getDatabaseData()
    {
        // 设置响应头确保UTF-8编码
        header('Content-Type: application/json; charset=utf-8');

        try {
            $result = [
                'categories_data' => [],
                'symptoms_data' => [],
                'formulas_data' => []
            ];

            // 获取分类数据（最近10条）
            $categories = Db::name('symptom_categories')
                ->order('created_at desc')
                ->limit(10)
                ->select();

            foreach ($categories as $category) {
                $result['categories_data'][] = [
                    'id' => $category['id'],
                    'name_zh_cn' => $category['name_zh_cn'],
                    'name_en_us' => $category['name_en_us'],
                    'name_ja_jp' => $category['name_ja_jp'],
                    'name_be_by' => $category['name_be_by'],
                    'description_zh_cn' => $category['description_zh_cn'],
                    'description_en_us' => $category['description_en_us'],
                    'description_ja_jp' => $category['description_ja_jp'],
                    'description_be_by' => $category['description_be_by'],
                    'created_at' => $category['created_at']
                ];
            }

            // 获取症状数据（最近15条）
            $symptoms = Db::name('symptoms')
                ->order('created_at desc')
                ->limit(15)
                ->select();

            foreach ($symptoms as $symptom) {
                // 获取对应的分类名称
                $categoryName = '';
                if (!empty($symptom['category_id'])) {
                    $categoryInfo = Db::name('symptom_categories')
                        ->where('id', $symptom['category_id'])
                        ->find();
                    if ($categoryInfo) {
                        $categoryName = $categoryInfo['name_zh_cn'];
                    }
                }

                $result['symptoms_data'][] = [
                    'id' => $symptom['id'],
                    'category_id' => $symptom['category_id'],
                    'category_name' => $categoryName,
                    'name_zh_cn' => $symptom['name_zh_cn'],
                    'name_en_us' => $symptom['name_en_us'],
                    'name_ja_jp' => $symptom['name_ja_jp'],
                    'name_be_by' => $symptom['name_be_by'],
                    'description_zh_cn' => $symptom['description_zh_cn'],
                    'description_en_us' => $symptom['description_en_us'],
                    'description_ja_jp' => $symptom['description_ja_jp'],
                    'description_be_by' => $symptom['description_be_by'],
                    'keywords_zh_cn' => $symptom['keywords_zh_cn'] ?? '',
                    'created_at' => $symptom['created_at']
                ];
            }

            // 获取汤药数据（最近10条）
            $formulas = Db::name('formulas')
                ->order('created_at desc')
                ->limit(10)
                ->select();

            foreach ($formulas as $formula) {
                // 解析症状ID并获取症状名称
                $mainSymptomNames = [];
                $sideSymptomNames = [];

                if (!empty($formula['main_symptom_ids'])) {
                    $mainSymptomIds = explode(',', $formula['main_symptom_ids']);
                    foreach ($mainSymptomIds as $id) {
                        if (!empty(trim($id)) && is_numeric(trim($id))) {
                            $symptomInfo = Db::name('symptoms')
                                ->where('id', trim($id))
                                ->find();
                            if ($symptomInfo) {
                                $mainSymptomNames[] = $symptomInfo['name_zh_cn'];
                            }
                        }
                    }
                }

                if (!empty($formula['side_symptom_ids'])) {
                    $sideSymptomIds = explode(',', $formula['side_symptom_ids']);
                    foreach ($sideSymptomIds as $id) {
                        if (!empty(trim($id)) && is_numeric(trim($id))) {
                            $symptomInfo = Db::name('symptoms')
                                ->where('id', trim($id))
                                ->find();
                            if ($symptomInfo) {
                                $sideSymptomNames[] = $symptomInfo['name_zh_cn'];
                            }
                        }
                    }
                }

                $result['formulas_data'][] = [
                    'id' => $formula['id'],
                    'name_zh_cn' => $formula['name_zh_cn'],
                    'name_en_us' => $formula['name_en_us'],
                    'name_ja_jp' => $formula['name_ja_jp'],
                    'name_be_by' => $formula['name_be_by'],
                    'description_zh_cn' => $formula['description_zh_cn'],
                    'description_en_us' => $formula['description_en_us'],
                    'description_ja_jp' => $formula['description_ja_jp'],
                    'description_be_by' => $formula['description_be_by'],
                    'ingredients_zh_cn' => $formula['ingredients_zh_cn'],
                    'usage_method_zh_cn' => $formula['usage_method_zh_cn'],
                    'effects_zh_cn' => $formula['effects_zh_cn'],
                    'main_symptom_names' => implode('、', $mainSymptomNames),
                    'side_symptom_names' => implode('、', $sideSymptomNames),
                    'created_at' => $formula['created_at']
                ];
            }

            // 确保返回的数据是UTF-8安全的
            $safeResult = $this->ensureUtf8Safe($result);

            return json([
                'code' => 200,
                'msg' => '数据库数据获取成功',
                'data' => $safeResult
            ]);

        } catch (\Exception $e) {
            Log::error('获取数据库数据失败：' . $e->getMessage());

            return json([
                'code' => 500,
                'msg' => '获取数据库数据失败：' . mb_convert_encoding($e->getMessage(), 'UTF-8', 'UTF-8'),
                'data' => null
            ]);
        }
    }
}
